package classrecord.classes.database.studentnumbertbl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import org.apache.commons.dbutils.DbUtils;

import classrecord.classes.database.common.IEveryTableDao;

public class STUDENTNUMBERTBLDao implements IEveryTableDao {

	private static final long serialVersionUID = 7827118236991221204L;

	private STUDENTNUMBERTBLDomain studentnumbertblDomain;
	private STUDENTNUMBERTBLDomain recordFoundDomain;

	private Connection connection;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;

	private final String RECORD_NUMBER = STUDENTNUMBERTBLColumn.RECORD_NUMBER.get();
	private final String STUDENT_NUMBER = STUDENTNUMBERTBLColumn.STUDENT_NUMBER.get();
	private final String IUD_FLAG = STUDENTNUMBERTBLColumn.IUD_FLAG.get();
	private final String CREATE_PID = STUDENTNUMBERTBLColumn.CREATE_PID.get();
	private final String CREATE_DATETIME = STUDENTNUMBERTBLColumn.CREATE_DATETIME.get();
	private final String DELETE_PID = STUDENTNUMBERTBLColumn.DELETE_PID.get();
	private final String DELETE_TIMESTAMP = STUDENTNUMBERTBLColumn.DELETE_TIMESTAMP.get();
	private final String UPDATE_PID = STUDENTNUMBERTBLColumn.UPDATE_PID.get();
	private final String UPDATE_TIMESTAMP = STUDENTNUMBERTBLColumn.UPDATE_TIMESTAMP.get();
	private final String LOGICAL_DELETE_FLAG = STUDENTNUMBERTBLColumn.LOGICAL_DELETE_FLAG.get();

	public STUDENTNUMBERTBLDomain getStudentnumbertblDomain() {
		return studentnumbertblDomain;
	}

	public void setStudentnumbertblDomain(STUDENTNUMBERTBLDomain studentnumbertblDomain) {
		this.studentnumbertblDomain = studentnumbertblDomain;
	}

	public STUDENTNUMBERTBLDomain getRecordFoundDomain() {
		return recordFoundDomain;
	}

	public void setConnection(Connection connection) {
		this.connection = connection;
	}

	@Override
	public boolean add() {
		boolean wasInserted = false;
		
		String studentNumber = studentnumbertblDomain.getStudentNumber();
		
		String insertSql = "INSERT INTO STUDENTNUMBERTBL (STUDENT_NUMBER) VALUES (?);";
		
		try {			
			preparedStatement = connection.prepareStatement(insertSql);
			preparedStatement.setString(1, studentNumber);
			
			int recordInserted = preparedStatement.executeUpdate();
			wasInserted = recordInserted > 0;

		} catch (SQLException sqle) {
			sqle.printStackTrace();
			
		} finally {
			DbUtils.closeQuietly(preparedStatement);

		}

		return wasInserted;
	}

	@Override
	public boolean update() {
		boolean wasUpdated = false;
		
		int recordNumber = studentnumbertblDomain.getRecordNumber();
		String studentNumber = studentnumbertblDomain.getStudentNumber();
		
		String updateSql = "UPDATE STUDENTNUMBERTBL SET STUDENT_NUMBER = ? WHERE RECORD_NUMBER = ? AND LOGICAL_DELETE_FLAG = 0;";	

		try {			
			preparedStatement = connection.prepareStatement(updateSql);
			preparedStatement.setString(1, studentNumber);
			preparedStatement.setInt(2, recordNumber);
						
			int recordUpdated = preparedStatement.executeUpdate();			
			wasUpdated = recordUpdated > 0;


		} catch (SQLException sqle) {
			sqle.printStackTrace();

		} finally {
			DbUtils.closeQuietly(preparedStatement);
			
		}
		
		return wasUpdated;
	}

	@Override
	public boolean physicalDelete() {
		boolean wasPhysicallyDeleted = false;
		
		int recordNumber = studentnumbertblDomain.getRecordNumber();
		String studentNumber = studentnumbertblDomain.getStudentNumber();

		String physicallyDeleteSql = "DELETE FROM STUDENTNUMBERTBL WHERE STUDENT_NUMBER = ? AND RECORD_NUMBER = ?;";	

		try {
			preparedStatement = connection.prepareStatement(physicallyDeleteSql);
			preparedStatement.setString(1, studentNumber);
			preparedStatement.setInt(2, recordNumber);
						
			int physicallyDeletedRecord = preparedStatement.executeUpdate();
			wasPhysicallyDeleted = physicallyDeletedRecord > 0;

		} catch (SQLException sqle) {
			sqle.printStackTrace();

		} finally {
			DbUtils.closeQuietly(preparedStatement);

		}
		
		return wasPhysicallyDeleted;
	}

	@Override
	public int getPhysicalRecordCount() {
		int physicalRecordCount = 0;
		
		String getPhysicalRecordSql = "SELECT COUNT(*) FROM STUDENTNUMBERTBL;";
		System.out.println("getPhysicalRecordSql: " + getPhysicalRecordSql);
		
		try {
			preparedStatement = connection.prepareStatement(getPhysicalRecordSql);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				System.out.println("resultSet.next()");
				physicalRecordCount = resultSet.getInt(1);
			
			} else {
				System.out.println("error: could not get the record counts");
			
			}

		} catch (SQLException sqle) {
			sqle.printStackTrace();

		} finally {
			DbUtils.closeQuietly(resultSet);
			DbUtils.closeQuietly(preparedStatement);

		}

		return physicalRecordCount;
	}
	
	@Override
	public int getLogicalRecordCount() {
		int logicalRecordCount = 0;
		
		String getLogicalRecordSql = "SELECT COUNT(*) FROM STUDENTNUMBERTBL WHERE LOGICAL_DELETE_FLAG != 1;";
		System.out.println("getLogicalRecordSql: " + getLogicalRecordSql);
		
		try {
			
			preparedStatement = connection.prepareStatement(getLogicalRecordSql);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				System.out.println("resultSet.next()");
				logicalRecordCount = resultSet.getInt(1);
			
			} else {
				System.out.println("error: could not get the record counts");
			
			}

			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		
		} finally {
			DbUtils.closeQuietly(resultSet);
			DbUtils.closeQuietly(preparedStatement);
		
		}

		return logicalRecordCount;
	}

	/* ***** Logically retrieve the details of a record. **********************
	 * [1]: This would retrieve the record of a corresponding row by asserting
	 * 		specific condition/s.
	 * [2]: The record's LOGICAL_DELETE_FLAG should not be equal to 1 for the
	 * 		details to be retrievable.
	 * ***********************************************************************/
	public STUDENTNUMBERTBLDomain logicalRetrieve() {
		String studentNumber = studentnumbertblDomain.getStudentNumber();
		
		String logicalRetrieveSql = "SELECT * FROM STUDENTNUMBERTBL WHERE STUDENT_NUMBER = ? AND LOGICAL_DELETE_FLAG != 1;";
		
		try {
			preparedStatement = connection.prepareStatement(logicalRetrieveSql);
			preparedStatement.setString(1, studentNumber);
			resultSet = preparedStatement.executeQuery();			
			recordFoundDomain = new STUDENTNUMBERTBLDomain();
			
			while (resultSet.next()) {
				
				int recordNumber = resultSet.getInt(RECORD_NUMBER);
				recordFoundDomain.setRecordNumber(recordNumber);
				
				String studentNumberFromResultSet = resultSet.getString(STUDENT_NUMBER);
				recordFoundDomain.setStudentNumber(studentNumberFromResultSet);
				
				int iudFlag = resultSet.getInt(IUD_FLAG);
				recordFoundDomain.setIudFlag(iudFlag);
				
				int logicalDeleteFlag = resultSet.getInt(LOGICAL_DELETE_FLAG);
				recordFoundDomain.setLogicalDeleteFlag(logicalDeleteFlag);
				
				String createPid = resultSet.getString(CREATE_PID);
				recordFoundDomain.setCreatePid(createPid);
				
				Timestamp createDateTime = resultSet.getTimestamp(CREATE_DATETIME);
				recordFoundDomain.setCreateDateTime(createDateTime);
				
				String deletePid = resultSet.getString(DELETE_PID);
				recordFoundDomain.setDeletePid(deletePid);
				
				Timestamp deleteTimestamp = resultSet.getTimestamp(DELETE_TIMESTAMP);
				recordFoundDomain.setDeleteTimestamp(deleteTimestamp);
				
				String updatePid = resultSet.getString(UPDATE_PID);
				recordFoundDomain.setUpdatePid(updatePid);
				
				Timestamp updateTimestamp = resultSet.getTimestamp(UPDATE_TIMESTAMP);
				recordFoundDomain.setUpdateTimestamp(updateTimestamp);
								
			}
			
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
	

		} finally {
			DbUtils.closeQuietly(resultSet);
			DbUtils.closeQuietly(preparedStatement);
		
		}
		
		// recordNumber will be set to 0 if no records found.
		return recordFoundDomain;
	}
	
	/* ***** Physically retrieve the details of a record.**********************
	 * [1]: Only the administrator is advise to call this method.
	 * [2]: Even a logically deleted record could be view once this method
	 * 		has been called.
	 * ***********************************************************************/
	public STUDENTNUMBERTBLDomain physicalRetrieve() {
		String studentNumber = studentnumbertblDomain.getStudentNumber();
		
		String physicalRetrieveSql = "SELECT * FROM STUDENTNUMBERTBL WHERE STUDENT_NUMBER = ?;";
		
		try {
			preparedStatement = connection.prepareStatement(physicalRetrieveSql);
			preparedStatement.setString(1, studentNumber);
			resultSet = preparedStatement.executeQuery();			
			recordFoundDomain = new STUDENTNUMBERTBLDomain();
			
			while (resultSet.next()) {
				
				int recordNumber = resultSet.getInt(RECORD_NUMBER);
				recordFoundDomain.setRecordNumber(recordNumber);
				
				String studentNumberFromResultSet = resultSet.getString(STUDENT_NUMBER);
				recordFoundDomain.setStudentNumber(studentNumberFromResultSet);
				
				int iudFlag = resultSet.getInt(IUD_FLAG);
				recordFoundDomain.setIudFlag(iudFlag);
				
				int logicalDeleteFlag = resultSet.getInt(LOGICAL_DELETE_FLAG);
				recordFoundDomain.setLogicalDeleteFlag(logicalDeleteFlag);
				
				String createPid = resultSet.getString(CREATE_PID);
				recordFoundDomain.setCreatePid(createPid);
				
				Timestamp createDateTime = resultSet.getTimestamp(CREATE_DATETIME);
				recordFoundDomain.setCreateDateTime(createDateTime);
				
				String deletePid = resultSet.getString(DELETE_PID);
				recordFoundDomain.setDeletePid(deletePid);
				
				Timestamp deleteTimestamp = resultSet.getTimestamp(DELETE_TIMESTAMP);
				recordFoundDomain.setDeleteTimestamp(deleteTimestamp);
				
				String updatePid = resultSet.getString(UPDATE_PID);
				recordFoundDomain.setUpdatePid(updatePid);
				
				Timestamp updateTimestamp = resultSet.getTimestamp(UPDATE_TIMESTAMP);
				recordFoundDomain.setUpdateTimestamp(updateTimestamp);
								
			}
	
			
		} catch (SQLException sqle) {
			sqle.printStackTrace();

		} finally {
			DbUtils.closeQuietly(resultSet);
			DbUtils.closeQuietly(preparedStatement);
		

		}
		
		// recordNumber will be set to 0 if no records found.
		return recordFoundDomain;
	}

	@Override
	public boolean logicalDelete() {
		boolean wasLogicallyDeleted = false;
		
		int recordNumber = studentnumbertblDomain.getRecordNumber();
		String studentNumber = studentnumbertblDomain.getStudentNumber();

		String logicallyDeleteSql = "UPDATE STUDENTNUMBERTBL SET LOGICAL_DELETE_FLAG = 1 WHERE STUDENT_NUMBER = ? AND RECORD_NUMBER = ?;";
		System.out.println("logicallyDeleteSql: " + logicallyDeleteSql);		

		try {
			preparedStatement = connection.prepareStatement(logicallyDeleteSql);
			preparedStatement.setString(1, studentNumber);
			preparedStatement.setInt(2, recordNumber);
						
			int logicallyDeletedRecord = preparedStatement.executeUpdate();
			System.out.println("logicallyDeletedRecord: " + logicallyDeletedRecord);
			
			wasLogicallyDeleted = logicallyDeletedRecord > 0;

		} catch (SQLException sqle) {
			sqle.printStackTrace();
			
		} finally {
			DbUtils.closeQuietly(preparedStatement);

		}
		
		return wasLogicallyDeleted	;
	}

}
